use crate::dao::sqlserver::sql_server_dao;
use crate::dao::sqlserver2sqlite::sqlserver2sqlite_auto;
use crate::sys::global::{to_global_result, GlobalResult};
use serde_json::Value;
use std::collections::{HashMap, HashSet};
use std::sync::Arc;
use std::sync::atomic::AtomicU32;
use askama::filters::format;
use tokio::fs::File;
use tokio::io::AsyncWriteExt;

const HOST: &str = "192.168.6.199";
const PORT: u16 = 1433;
const DATA_BASE: &str = "suoyinshuju1";
const USER: &str = "sa";
const PASSWORD: &str = "Yiscn123456@";

///导出数据
pub async fn copy_to_sqlite(table_name: &str, size: usize) -> GlobalResult<()> {
    let mut client = sql_server_dao::get_client(HOST, PORT, DATA_BASE, USER, PASSWORD)
        .await
        .unwrap();

    sqlserver2sqlite_auto(
        &mut client,
        "xxzx_id",
        0,
        table_name,
        format!("{}.db", table_name).as_str(),
        size,
    )
    .await
}

///检查文件是否在路径集合中存在
/// @param dir  文件夹路径
pub async fn check_file_exists(dir: &str) -> GlobalResult<()> {
    let dir = dir.replace("\\", "/");
    let mut set = HashSet::with_capacity(800 * 10000);
    let r = my_util_lib::tokio_file::read_by_line("file_paths.txt", |line| {
        let mut line = line.replace("\\", "/");
        if line.starts_with("/") {
            line = line[1..].to_string();
        }
        set.insert(line);
        true
    }).await;
    to_global_result(r)?;
    log::info!("总数:{}", set.len());



    let  count  = AtomicU32::new(0);
    let r = my_util_lib::tokio_file::count_sub_file(dir.to_string(), Arc::new(count)).await;
    let paths = to_global_result(r)?;
    log::info!("文件夹下文件总数；{}", paths.len());
    let mut no_exists_count = 0;
    
    let mut out_file = File::create(format!("files{}.txt", dir).replace(":", "_").replace("/","_")).await?;
    
    for path in paths {
        let p = path.replace("\\", "/").replace(format!("{}/", dir).as_str(), "");
        if !set.contains(&p) {
            log::info!("not exists: {}", p);
            no_exists_count += 1;
        }
        out_file.write_all(format!("{}\n", p).as_bytes()).await?;
    }
    out_file.flush().await?;
    log::info!("不存在文件数量:{}", no_exists_count);


    Ok(())
}

///获取文件路径
pub async fn get_file_path() -> GlobalResult<()> {
    let mut client =
        to_global_result(sql_server_dao::get_client(HOST, PORT, DATA_BASE, USER, PASSWORD).await)?;

    let mut out_file = File::create("file_paths.txt").await?;
    let mut xxzx_id = 0;
    let mut progress = 0;
    loop {
        log::info!("xxx_zx_id:{}, progress:{}", xxzx_id, progress);
        let sql = format!(
            "SELECT TOP 4000
      [xxzx_id]
	  ,CONCAT(UPDATETIME,'/',PDFFILENAME)as FULLPATH
  FROM [suoyinshuju1].[dbo].[ES]  where  xxzx_id > {} order by xxzx_id asc",
            xxzx_id
        );
        let r = to_global_result(sql_server_dao::select_list(&sql, &mut client).await)?;

        if r.1.is_empty() {
            break;
        }
        progress += r.1.len();

        for row in r.1.iter() {
            xxzx_id = row.get(0).unwrap().as_i64().unwrap();

            if let Some(v) = row.get(1) {
                if let Value::String(s) = v {
                    out_file.write_all(format!("{}\n", s).as_bytes()).await?;
                }
            }
        }
        out_file.flush().await?;
    }

    Ok(())
}

///统计附件重复的数据
pub async fn check_es_repeat_files() -> GlobalResult<()> {
    let mut client =
        to_global_result(sql_server_dao::get_client(HOST, PORT, DATA_BASE, USER, PASSWORD).await)?;

    let mut path_map: HashMap<String, String> = std::collections::HashMap::new();
    let mut xxzx_id = 0;
    let mut progress = 0;
    loop {
        log::info!("xxx_zx_id:{}, progress:{}", xxzx_id, progress);
        let sql = format!(
            "SELECT TOP 1000
      [xxzx_id]
	  ,CONCAT(UPDATETIME,'/',PDFFILENAME)as FULLPATH
  FROM [suoyinshuju1].[dbo].[ES]  where  xxzx_id > {} order by xxzx_id asc",
            xxzx_id
        );
        let r = to_global_result(sql_server_dao::select_list(&sql, &mut client).await)?;

        if r.1.is_empty() {
            break;
        }
        progress += r.1.len();

        for row in r.1.iter() {
            xxzx_id = row.get(0).unwrap().as_i64().unwrap();

            if let Some(v) = row.get(1) {
                if let Value::String(s) = v {
                    if path_map.contains_key(s) {
                        let v = path_map.get(s).unwrap();
                        path_map.insert(s.clone(), format!("{},{}", v, xxzx_id));
                        log::info!("重复附件：{},{}", xxzx_id, s);
                    } else {
                        path_map.insert(s.clone(), format!("{}", xxzx_id));
                    }
                }
            }
        }
    }
    log::info!("读取完成, 附件总数:{}", path_map.len());

    let mut file = File::create("repeat.tx").await?;

    let mut repeat_count = 0;

    for (path, xxzx_ids) in path_map.iter() {
        if xxzx_ids.contains(",") {
            repeat_count += 1;
            file.write_all(format!("{}, {}", path, xxzx_ids).as_bytes())
                .await?;
        }
    }
    log::info!("重复数:{}", repeat_count);
    file.flush().await?;
    Ok(())
}

///检查不存在的文件数量
pub async fn check_es_data_files(mut xxzx_id: i64) -> GlobalResult<()> {
    let dirs = "E:/盘1/期刊/ES/,G:/盘5/期刊/es/,F:/盘2/期刊/ES/,F:/盘3/期刊/ES/"
        .split(",")
        .map(|dir| dir.to_string())
        .collect::<Vec<String>>();
    let mut client =
        to_global_result(sql_server_dao::get_client(HOST, PORT, DATA_BASE, USER, PASSWORD).await)?;

    let mut no_exists_file_count = 0;
    let mut progress = 0;
    loop {
        log::info!("xxx_zx_id:{}, progress:{}", xxzx_id, progress);
        let sql = format!(
            "SELECT TOP 1000
      [xxzx_id]
	  ,CONCAT(UPDATETIME,'/',PDFFILENAME)as FULLPATH
  FROM [suoyinshuju1].[dbo].[ES]  where  xxzx_id > {} order by xxzx_id asc",
            xxzx_id
        );
        let r = to_global_result(sql_server_dao::select_list(&sql, &mut client).await)?;

        if r.1.is_empty() {
            break;
        }
        progress += r.1.len();

        for row in r.1.iter() {
            xxzx_id = row.get(0).unwrap().as_i64().unwrap();

            if let Some(v) = row.get(1) {
                let mut paths = String::new();
                if let Value::String(s) = v {
                    let mut exists = false;
                    let mut path_count = 0;
                    for dir in dirs.iter() {
                        let path = format!("{}{}", dir, s).replace("\\", "/");
                        paths.push_str(&path);
                        paths.push_str("; ");
                        let path_exists = std::path::Path::new(&path).exists();
                        if path_exists {
                            exists = true;
                            path_count += 1;
                        }
                    }
                    if path_count > 1 {
                        log::info!("重复附件:{}", s);
                    }
                    if !exists {
                        no_exists_file_count += 1;
                        log::info!(
                            "no file xxzx_id:{}, file_path:{}, no_exists_file_count:{}",
                            xxzx_id,
                            paths,
                            no_exists_file_count
                        );
                    }
                }
            }
        }
    }
    Ok(())
}
